﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace DataOperation
{
    public class ExcelOperation
    {

        public static System.Data.DataTable LoadDataFromExcel(string filePath, string sheetName)
        {
            try
            {
                string strConn;
                string type = filePath.Substring(filePath.Length - 4, 4);
                if (!type.Equals("xlsx", StringComparison.InvariantCultureIgnoreCase))
                {
                    strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath +
                        ";Extended Properties='Excel 8.0; HDR=False; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
                }
                else
                {
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath +
                        ";Extended Properties='Excel 12.0; HDR=False; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
                }
                DataSet ds = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}$]", sheetName), conn);
                    odda.Fill(ds, sheetName);
                    conn.Close();
                }
                return ds.Tables[sheetName];
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }

        public static string[] GetExcelSheetNames(string filePath)
        {
            System.Data.DataTable dt = null;
            try
            {
                string strConn;
                string type = filePath.Substring(filePath.Length - 4, 4);
                if (!type.Equals("xlsx", StringComparison.InvariantCultureIgnoreCase))
                {
                    strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath +
                        ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
                }
                else
                {
                    strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filePath +
                        ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
                }
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (dt == null)
                    {
                        return null;
                    }
                    string[] excelSheets = new string[dt.Rows.Count];
                    int i = 0;
                    foreach (DataRow row in dt.Rows)
                    {
                        excelSheets[i] = row["TABLE_NAME"].ToString();
                        i++;
                    }
                    return excelSheets;
                }
            }
            finally
            {
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app =
                new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                Workbook wBook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
                int rowNum = excelTable.Rows.Count;
                int colNum = excelTable.Columns.Count;

                //表头
                for (int i = 0; i < colNum; i++)
                {
                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
                }

                //内容
                if (excelTable.Rows.Count > 0)
                {
                    for (int i = 0; i < rowNum; i++)
                    {
                        for (int j = 0; j < colNum; j++)
                        {
                            string str = excelTable.Rows[i][j].ToString();
                            wSheet.Cells[i + 2, j + 1] = str;
                        }
                    }
                }

                //设置禁止弹出保存和覆盖的询问提示框 
                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;

                wBook.SaveAs(filePath);
                wBook.Close();
                app.Quit();
                app = null;
                return true;
            }
            catch (Exception err)
            {
                MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                GC.Collect();
            }
        }

        public static void makeChart(string filePath)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            Workbook xlWorkBook;
            Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //add data 
            xlWorkSheet.Cells[1, 1] = "";
            xlWorkSheet.Cells[1, 2] = "Student1";
            xlWorkSheet.Cells[1, 3] = "Student2";
            xlWorkSheet.Cells[1, 4] = "Student3";

            xlWorkSheet.Cells[2, 1] = "Term1";
            xlWorkSheet.Cells[2, 2] = "80";
            xlWorkSheet.Cells[2, 3] = "65";
            xlWorkSheet.Cells[2, 4] = "45";

            xlWorkSheet.Cells[3, 1] = "Term2";
            xlWorkSheet.Cells[3, 2] = "78";
            xlWorkSheet.Cells[3, 3] = "72";
            xlWorkSheet.Cells[3, 4] = "60";

            xlWorkSheet.Cells[4, 1] = "Term3";
            xlWorkSheet.Cells[4, 2] = "82";
            xlWorkSheet.Cells[4, 3] = "80";
            xlWorkSheet.Cells[4, 4] = "65";

            xlWorkSheet.Cells[5, 1] = "Term4";
            xlWorkSheet.Cells[5, 2] = "75";
            xlWorkSheet.Cells[5, 3] = "82";
            xlWorkSheet.Cells[5, 4] = "68";

            Range chartRange;

            ChartObjects xlCharts = (ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            ChartObject myChart = (ChartObject)xlCharts.Add(10, 80, 300, 250);
            Chart chartPage = myChart.Chart;

            chartRange = xlWorkSheet.get_Range("A1", "d5");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = XlChartType.xlColumnClustered;
            xlApp.AlertBeforeOverwriting = false;
            xlApp.DisplayAlerts = false;

            xlWorkBook.SaveAs(filePath);//(XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }


        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        /// <summary>
        /// Export datagrid to excel
        /// </summary>
        /// <param name="page">source web page</param>
        /// <param name="datagrid">source datagrid</param>
        /// <param name="filename">export file name</param>
        //public static void ExportToExcel(System.Web.UI.Page page, GridView gv, string filename)
        //{
        //    // To avoid date time string is formatted with default style used by Excel.
        //    //const string style = @"<style>.format {mso-number-format:\@;}</style>";

        //    //foreach (System.Web.UI.WebControls.DataControlField col in gv.Columns)
        //    //{
        //    //    if (col.HeaderText == "Action" || col.HeaderText == "Active" 
        //    //        || col.HeaderText == "PreContractID"
        //    //        || col.HeaderText == "PreContractNo"
        //    //        || col.HeaderText == "")
        //    //        col.Visible = false;

        //    //}


        //    page.Response.Clear();
        //    page.Response.Buffer = true;
        //    // Set the content type to Excel.
        //    page.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
        //    // Remove the charset from the Content-Type header.
        //    page.Response.ContentEncoding = System.Text.Encoding.UTF8;
        //    //page.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312>"); 

        //    page.Response.ContentType = "application/vnd.ms-excel";


        //    // Turn off the view state.
        //    page.EnableViewState = false;

        //    //System.IO.StringWriter tw = new System.IO.StringWriter();
        //    //System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        //    StringBuilder sb = new StringBuilder();

        //    sb.Append("<table  border='1'>");
        //    sb.Append("<tr>");
        //    for (int i = 0; i < gv.Columns.Count; i++)
        //    {
        //        if (i > 2)
        //        {
        //            sb.Append(string.Format("<td align='center'><b>{0}</b></td>", gv.HeaderRow.Cells[i].Text.ToString()));
        //        }
        //    }
        //    sb.Append("</tr>");

        //    foreach (GridViewRow gr in gv.Rows)
        //    {
        //        sb.Append("<tr>");
        //        for (int i = 0; i < gr.Cells.Count; i++)
        //        {
        //            if (i > 2)
        //            {
        //                //yuliang
        //                //if (i == 5)
        //                //    sb.Append("<td>" + gr.Cells[1].Text.ToString() + "</td>");
        //                //else
        //                string text = gr.Cells[i].Text;
        //                if (text.Contains("-"))
        //                {
        //                    sb.Append("<td class=\"format\">" + text.ToString() + "</td>");
        //                }
        //                else
        //                {
        //                    sb.Append("<td>" + text.ToString() + "</td>");
        //                }
        //            }
        //        }
        //        sb.Append("</tr>");
        //    }
        //    sb.Append("</table>");

        //    //gv.RenderControl(hw);
        //    // Write the HTML back to the browser.
        //    // page.Response.Write(style);

        //    //page.Response.Write(tw.ToString());
        //    page.Response.Write(sb.ToString());

        //    // End the response.
        //    page.Response.End();
        //}
    }
}
